package jdbclearn;

import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;

import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
@WebServlet(name = "TestJdbc",value = "/TestJdbc")
public class TestJdbc extends HttpServlet {
        String databaseName="EMP";

        String name = "root";

        String password = "123456";

        String url = "jdbc:mysql://47.111.186.126:3306/"+databaseName
                +"?useSSL=false&&allowPublicKeyRetrieval=true&serverTimezone=UTC";
        String JDBC_DRIVER="com.mysql.cj.jdbc.Driver";

        @Override
        protected void doGet(HttpServletRequest request,
                         HttpServletResponse response)
            throws ServletException, IOException{
            Connection conn = null;
            Statement stmt=null;
            try{
                Class.forName(JDBC_DRIVER);

                System.out.println("链接数据库..");
                conn=DriverManager.getConnection(url,name,password);

                System.out.println("实例化Statement对象..");
                stmt=conn.createStatement();
                String sql;
                sql="SELECT id , first, last, age FROM Employees";
                ResultSet rs= stmt.executeQuery(sql);

                while (rs.next()){
                    int id = rs.getInt("id");
                    int age = rs.getInt("age");
                    String first = rs.getString("first");
                    String last=rs.getString("last");

                    PrintWriter out = response.getWriter();
                    out.print("ID"+id);
                    out.print(",年龄:"+age);
                    out.print(",姓:"+first);
                    out.print(",名:"+last);
                    out.print("</br>");
                }
                rs.close();
                stmt.close();
                conn.close();
            }catch (SQLException se){
                se.printStackTrace();
            }catch (Exception e){
                e.printStackTrace();
            }finally {
                try{
                    if (stmt!=null)stmt.close();
                }catch (SQLException se2){
                }
            }
            try {
                if (conn!=null)conn.close();
            }catch (SQLException se){
                se.printStackTrace();
            }
            System.out.println("Goodbye");
        }
}
